﻿using System;
using System.Text;

namespace FancyIdea.PingShui.Core.DbHelper
{
    /// <summary>
    /// 用于产生简单的SQL查询语句
    /// </summary>
    public class SimpleQueryBuilder
    {
        /// <summary>
        /// SQL语句中的字段列表部分
        /// </summary>
        public string FieldsString;

        /// <summary>
        /// SQL语句中的统计字段
        /// </summary>
        [Obsolete("")]
        public string FieldForCount
        {
            set
            {
                FieldsStringForCount = string.Format("count({0}) as count",
                    value);
            }
        }

        /// <summary>
        /// 用于统计的SQL语句字段部分
        /// </summary>
        public string FieldsStringForCount;

        /// <summary>
        /// SQL语句的FROM子句部分
        /// </summary>
        public string FromString;

        /// <summary>
        /// 分页SQL语句中的排序部分
        /// </summary>
        public string PageOrderString;

        /// <summary>
        /// 固定的条件
        /// </summary>
        public string[] FixedConditions;

        /// <summary>
        /// 动态条件，可随时更改
        /// </summary>
        public string[] Conditions;

        /// <summary>
        /// SQL语句中的排序部分
        /// </summary>
        public string OrderString;

        /// <summary>
        /// 判断一个数组是否null或者空
        /// </summary>
        /// <param name="array"></param>
        /// <returns></returns>
        public bool IsNullOrEmptyArray(object[] array)
        {
            return array == null || array.Length < 1;
        }

        private void AddConditions(StringBuilder builder)
        {
            if (IsNullOrEmptyArray(FixedConditions)
                && IsNullOrEmptyArray(Conditions))
            {
                return;
            }

            builder.Append("where ");
            if (!IsNullOrEmptyArray(FixedConditions))
            {
                builder.AppendLine(string.Join("\nand ", FixedConditions));
            }

            if (!IsNullOrEmptyArray(Conditions))
            {
                if (!IsNullOrEmptyArray(FixedConditions))
                {
                    builder.Append("and ");
                }
                builder.AppendLine(string.Join("\nand ", Conditions));
            }
        }

        /// <summary>
        /// 创建不含分页的SQL SELECT语句
        /// </summary>
        /// <returns></returns>
        public string BuildQuerySql()
        {
            return BuildQuerySql(0, 0);
        }

        /// <summary>
        /// 创建可分页的SQL SELECT语句
        /// </summary>
        /// <param name="page"></param>
        /// <param name="dataPerPage"></param>
        /// <returns></returns>
        public string BuildQuerySql(int page, int dataPerPage)
        {
            //page = 0; dataPerPage = 0;
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (page > 0)
            {
                builder.AppendFormat("row_number() over({0}) as fi_t_row,",
                    PageOrderString).AppendLine();
            }
            builder.AppendLine(FieldsString);
            builder.AppendLine(FromString);
            AddConditions(builder);

            if (page > 0)
            {
                int start = (page - 1) * dataPerPage + 1;
                int stop = page * dataPerPage;

                builder.Insert(0, "select * from (");
                builder.AppendFormat(") t where fi_t_row between {0} and {1}",
                    start, stop).AppendLine();
            }

            builder.AppendLine(OrderString);
            return builder.ToString();
        }

        /// <summary>
        /// 创建带条件的统计语句
        /// </summary>
        /// <returns></returns>
        public string BuildQueryStatisticSql()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ").AppendLine(FieldsStringForCount);
            builder.AppendLine(FromString);
            AddConditions(builder);
            builder.AppendLine(OrderString);

            return builder.ToString();
        }
    }
}
